package com.neusoft.lohas.common.utils.excel;
/*
 * note:2011-05-26 from xjh
 * download jxl.jar from follow url ,then join jar into lib 
 * http://www.andykhan.com/jexcelapi/download.html
 * reference: 
 * http://blog.csdn.net/xlbest0711/archive/2009/06/11/4260156.aspx
 */
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.springframework.stereotype.Service;

@Service
public class ExcelManager {
	public void writeExcel(File f, String sheetName, List<String> columns, Map<String, List<String>> data) throws Exception {
	//public void writeExcel(OutputStream f) throws Exception {

		int ColumnCount = columns.size();
		int RowCount = data.size();

		jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(f);
		jxl.write.WritableSheet ws = wwb.createSheet(sheetName, 0);
		
		// fill caption
		for (int i = 0; i < ColumnCount; i++) {
			jxl.write.Label labelC = new jxl.write.Label(i, 0, columns.get(i));
			ws.addCell(labelC);
		}	
		ws.setRowView(0, 300);

		/*
		 * jxl.write.WritableFont wfc = new jxl.write.WritableFont(
		 * WritableFont.ARIAL, 20, WritableFont.BOLD, false,
		 * UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
		 * jxl.write.WritableCellFormat wcfFC = new
		 * jxl.write.WritableCellFormat( wfc);
		 * wcfFC.setBackground(jxl.format.Colour.RED); labelC = new
		 * jxl.write.Label(6, 0, "设置颜色背景", wcfFC); ws.addCell(labelC);
		 */

		// fill data
		int num = 0;
		for (List<String> values : data.values()) {
			num++;
			for (int j = 0; j < ColumnCount; j++) {
				jxl.write.Label labelC = new jxl.write.Label(j, num, values.get(j));
				ws.addCell(labelC);
			}
			ws.setRowView(num, 300);
		}

		// write into Excel sheet
		wwb.write();
		// close Excel sheet object
		wwb.close();
	}

	public void readExcel(File os, String sheetName) throws Exception {
		Workbook wb = Workbook.getWorkbook(os);
		Sheet s = wb.getSheet(sheetName);
		Cell c = s.getCell(0, 0);
		System.out.println(c.getContents());
	}
	
	public Map<String, List<String>> ReadMethod(String filename) throws Exception {
		try {
			Map<String, List<String>> data = new LinkedHashMap<String, List<String>>();
			// 首先构造一个输入流
			InputStream is = new FileInputStream(filename);
			// jxl的Workbook得到这个输入流
			jxl.Workbook rwb = Workbook.getWorkbook(is);

			// Workbook得到第一个sheet

			Sheet[] sheets = rwb.getSheets();// 获得当前Excel表共有几个sheet
			int p = sheets.length;
			//System.out.println("此表共有几个sheet------->" + p);
			int dataAllNum=0;
			int dataNum=0;
			for (int w = 0; w < p; w++) { // 将每个sheet中的内容全部读取出来
				dataAllNum=dataNum;
				dataNum=0;
				// 在从Excel中读取数据的时候不需要知道每个sheet有几行，有那多少列
				Sheet rs = rwb.getSheet(w);
				int cols = rs.getColumns();
				List<String> rowData = new ArrayList<String>();
				for (int i = 0; i < cols; i++) {
					Cell a00 = rs.getCell(i, 0);
					String stra00 = a00.getContents();
					rowData.add(stra00.replaceAll(" ", ""));
					//System.out.print("*****" + stra00 + "*****");
				}
				data.put((dataAllNum+0)+"", rowData);
				dataNum++;
				//System.out.println("\n");
				int rows = rs.getRows();

				for (int d = 1; d < rows; d++) { // 行循环,Excel的行列是从（0，0）开始的
					rowData = new ArrayList<String>();
					int errorColNum = 0;
					for (int t = 0; t < cols; t++) { // 列循环
						Cell b00 = rs.getCell(t, d);
						String strb = b00.getContents();
						rowData.add(strb.trim());
						if (strb.trim()==null || strb.trim().equals("")) {
							errorColNum ++;
						}
						//System.out.print(strb + "********");
					}
					if(errorColNum != cols) {  //如果本行数据都为空，则不添加此行数据，否则添加
						data.put((dataAllNum+d)+"", rowData);
						dataNum++;
					}
					//System.out.println("\n");
				}
				//System.out.println("第" + (w + 1) + "个sheet结束" + "\n");
			}
			return data;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	// test writeExcel and readExcel method of Tool class
	public static void main(String[] args) throws Exception {
	}
}